SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
 CREATE PROCEDURE [dbo].[MaturityDateDistribution]
      
      @FundID VARCHAR(10)
      ,
      @StartDate DATETIME
 AS
      BEGIN
           
           DECLARE @ExportID int
           
           DECLARE @FundMktVal DECIMAL(38, 10)
           
		   SET @ExportID =
			  (SELECT MAX(ExportId)
			   FROM [FSFundModel].[Reporting].Export
			   WHERE FundId = @FundID
			   AND  NAVDate = @StartDate)
           
           SET @FundMktVal =
			   (SELECT CAST(f.MktValAssets AS DECIMAL (38,10))
			   FROM  [FSFundModel].[Reporting].Fund f
			   WHERE FundId  = @FundID
			   AND  ExportId = @ExportID)
           
           
           -----
           
           
           SELECT
                CASE
                     WHEN CAST(b.Period AS VARCHAR(50)) = '0'
                     THEN 'NA'
                     WHEN CAST(b.Period AS VARCHAR(50)) = '1'
                     THEN '<2'
                     WHEN CAST(b.Period AS VARCHAR(50)) = '2'
                     THEN '2-3'
                     WHEN CAST(b.Period AS VARCHAR(50)) = '3'
                     THEN '3-4'
                     WHEN CAST(b.Period AS VARCHAR(50)) = '4'
                     THEN '4-5'
                     WHEN CAST(b.Period AS VARCHAR(50)) = '5'
                     THEN '>5'
					 
                     END AS Period, 
				
				(SUM(CAST(b.mktval AS DECIMAL (38,10))*10000.0)/ (@FundMktVal*10000.0))/10000.0 AS Pct
				
				FROM
						
				(
				
				
				SELECT i.InstrName
					   , i.MaturityDate
					   , CAST(a.MktVal AS DECIMAL (38,10))*10000.0  AS mktval
					   , CASE
							  WHEN id.HasMaturity = 0  THEN 0
								   --WHEN id.InstrClass LIKE '%Equity%' THEN 0
							  WHEN i.MaturityDate < DATEADD(YEAR, 2, @StartDate)
							  THEN 1
							  WHEN i.MaturityDate >= DATEADD(YEAR, 2, @StartDate)
							  AND  i.MaturityDate  < DATEADD(YEAR, 3, @StartDate)
							  THEN 2
							  WHEN i.MaturityDate >= DATEADD(YEAR, 3, @StartDate)
							  AND  i.MaturityDate  < DATEADD(YEAR, 4, @StartDate)
							  THEN 3
							  WHEN i.MaturityDate >= DATEADD(YEAR, 4, @StartDate)
							  AND  i.MaturityDate  < DATEADD(YEAR, 5, @StartDate)
							  THEN 4
							  WHEN i.MaturityDate >= DATEADD(YEAR, 5, @StartDate)
							  THEN 5
							  ELSE 0 
                     
						 END AS Period
                
                
                
					FROM [FSFundModel].[Reporting].asset a
                     
						 INNER JOIN [FSFundModel].[Reporting].Instrument i
						 ON   a.ExportId     = i.ExportId
						 AND  a.InstrumentId = i.InstrumentId
						 INNER JOIN [FSFundModel].[Reporting].InstrumentDefinition id
						 ON   i.ExportId     = id.ExportId
						 AND  i.InstDefId    = id.InstDefId
					WHERE a.IsActiveEndOfDay = 1
					AND  a.ExportId          = @ExportID
					--AND id.InstrClass NOT LIKE 'Equity/Other%'
                     
						 --ORDEr BY tile ASC
                
                )
                AS b GROUP BY b.Period
      END
GO
EXEC sp_addextendedproperty N'VirtualFolder', N'Live/CharlieWaterman', 'SCHEMA', N'dbo', 'PROCEDURE', N'MaturityDateDistribution', NULL, NULL
GO
